package cn.jbolt.core.gen;

import cn.jbolt.core.db.dialect.JBoltSqlServerDialect;
import cn.jbolt.core.util.JBoltConsoleUtil;
import com.jfinal.kit.Kv;
import com.jfinal.plugin.activerecord.generator.ColumnMeta;
import com.jfinal.plugin.activerecord.generator.TableMeta;

import javax.sql.DataSource;
import java.sql.*;
import java.util.List;

public class _JBoltSqlServerMetaBuilder extends JBoltMetaBuilder {
	private Connection columnRemarkConn;

	public _JBoltSqlServerMetaBuilder(DataSource dataSource) {
		super(dataSource);
	}

	@Override
	protected void buildColumnMetas(TableMeta tableMeta) throws SQLException {
		String sql = dialect.forTableBuilderDoBuild(tableMeta.name);
		Statement stm = conn.createStatement();
		ResultSet rs = stm.executeQuery(sql);
		ResultSetMetaData rsmd = rs.getMetaData();
		int columnCount = rsmd.getColumnCount();

		Kv remarkKv = null;
		if (generateRemarks) {
			remarkKv = buildTabelColumnRemark(tableMeta.name);
		}

		ColumnMeta cm;
		for (int i = 1; i <= columnCount; i++) {
			cm = new ColumnMeta();
			cm.name = rsmd.getColumnName(i);
			String colClassName = rsmd.getColumnClassName(i);
			String typeStr = typeMapping.getType(colClassName);
			if (typeStr != null) {
				cm.javaType = typeStr;
			} else {
				int type = rsmd.getColumnType(i);
				if (type == Types.BINARY || type == Types.VARBINARY || type == Types.BLOB) {
					cm.javaType = "byte[]";
				} else if (type == Types.SMALLINT) {
					cm.javaType = "java.lang.Short";
				} else if (type == Types.CLOB || type == Types.NCLOB) {
					cm.javaType = "java.lang.String";
				} else {
					cm.javaType = "java.lang.String";
				}
			}
			// 特殊处理char(1) to Boolean
			if ("java.lang.String".equals(cm.javaType)) {
				int scale = rsmd.getScale(i); // 小数点右边的位数，值为 0 表示整数
				int precision = rsmd.getPrecision(i); // 最大精度
				if (scale == 0 && precision == 1 && JBoltProjectGenConfig.charToBoolean) {
					cm.javaType = "java.lang.Boolean";
				}
			}

			// 构造字段对应的属性名 attrName
			cm.attrName = buildAttrName(cm.name);
			// 备注字段赋值
			if (generateRemarks) {
				cm.remarks = remarkKv.getStr(cm.name);
			}
			colMap.put((tableMeta.baseModelName + "_" + cm.name).toLowerCase(), cm);
			tableMeta.columnMetas.add(cm);
		}

		rs.close();
		stm.close();

	}

	@Override
	protected ResultSet getTablesResultSet() throws SQLException {
		setDialect(new JBoltSqlServerDialect());
		if (generateView) {
			return dbMeta.getTables(conn.getCatalog(), null, "%", new String[]{"TABLE", "VIEW"});
		}
		return dbMeta.getTables(conn.getCatalog(), null, "%", new String[]{"TABLE"});	// 不支持 view 生成
	}

	@Override
	protected void buildTableNames(List<TableMeta> ret) throws SQLException {
		ResultSet rs = getTablesResultSet();
		while (rs.next()) {
			String schem = rs.getString("TABLE_SCHEM");
			String tableName = rs.getString("TABLE_Name");

			if (schem.equals("sys")) {
				JBoltConsoleUtil.printMessage(" Skip table :" + tableName + ",sys table");
				continue;
			}
			// 如果使用白名单（size>0），则不在白名单之中的都将被过滤
			if (whitelist.size() > 0 && !whitelist.contains(tableName)) {
				JBoltConsoleUtil.printMessageWithDate(" Skip table(not in whitelist) :" + tableName);
				continue ;
			}
			// 如果使用黑名单（size>0），则处在黑名单之中的都将被过滤
			if (blacklist.size() > 0 && blacklist.contains(tableName)) {
				JBoltConsoleUtil.printMessageWithDate(" Skip table(in blacklist) :" + tableName);
				continue ;
			}
			if (isSkipTable(tableName)) {
				JBoltConsoleUtil.printMessage(" Skip table(isSkipTable) :" + tableName);
				continue;
			}
			// jfinal 4.3 新增过滤 table 机制
			if (tableSkip != null && tableSkip.test(tableName)) {
				JBoltConsoleUtil.printMessageWithDate(" Skip table :" + tableName);
				continue;
			}
			TableMeta tableMeta = new TableMeta();
			tableMeta.name = tableName;
			if (generateRemarks) {
				tableMeta.remarks = getTabelRemark(tableName);
			}

			tableMeta.modelName = buildModelName(tableName);
			tableMeta.baseModelName = buildBaseModelName(tableMeta.modelName);
			ret.add(tableMeta);
		}
		rs.close();
		if (generateRemarks && columnRemarkConn != null && columnRemarkConn.isClosed() == false) {
			columnRemarkConn.close();
		}
	}

	protected String getTabelRemark(String table) throws SQLException {
		Statement statement = conn.createStatement();
		ResultSet rs = statement.executeQuery("SELECT cast(ds.value as varchar(200)) as remarks "
				+ " FROM sys.extended_properties ds" + " LEFT JOIN sysobjects tbs ON ds.major_id=tbs.id"
				+ " WHERE  ds.minor_id=0 and tbs.name='" + table + "'");
		String remark = null;
		if (rs.next()) {
			remark = rs.getString("remarks");
		}
		rs.close();
		return remark;
	}

	private Kv buildTabelColumnRemark(String table) throws SQLException {
		if (columnRemarkConn == null) {
			columnRemarkConn = dataSource.getConnection();
		}
		Statement statement = columnRemarkConn.createStatement();
		ResultSet rs = statement.executeQuery("SELECT  cast(col.name as varchar(200)) AS name ,"
				+ " cast(ISNULL(ep.[value], '') as varchar(200)) AS remarks" + " FROM    dbo.syscolumns col"
				+ " LEFT  JOIN dbo.systypes t ON col.xtype = t.xusertype"
				+ " inner JOIN dbo.sysobjects obj ON col.id = obj.id" + " AND obj.xtype = 'U'" + " AND obj.status >= 0"
				+ " LEFT  JOIN dbo.syscomments comm ON col.cdefault = comm.id"
				+ " LEFT  JOIN sys.extended_properties ep ON col.id = ep.major_id" + "      AND col.colid = ep.minor_id"
				+ "      AND ep.name = 'MS_Description'"
				+ " LEFT  JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_id" + " AND epTwo.minor_id = 0"
				+ " AND epTwo.name = 'MS_Description'" + " WHERE obj.name = '" + table + "'");
		Kv kv = Kv.create();
		while (rs.next()) {
			kv.set(rs.getString("name"), rs.getString("remarks"));
		}
		rs.close();
		return kv;
	}
}
